#!/usr/bin/env python
# encoding: utf-8
"""
#-------------------------------------------------------------------#
#                   CONFIDENTIAL --- CUSTOM STUDIOS                 #     
#-------------------------------------------------------------------#
#                                                                   #
#                   @Project Name : Globallawonline                #
#                                                                   #
#                   @File Name    : CjSqlToJg.py                      #
#                                                                   #
#                   @Programmer   : 李建                            #
#                                                                   #  
#                   @Start Date   : 2021/1/21 16:49                 #
#                                                                   #
#                   @Last Update  : 2021/1/21 16:49                 #
#                                                                   #
#-------------------------------------------------------------------#
# Classes:该类用于将采集服务器的Sql记录上传到加工服务器                     #
#                                                                   #
#-------------------------------------------------------------------#
"""
import datetime
import re

import pymysql

from settings import CJSQLCON, JGSQLCON, CJSQLTABLE, JGSQLTABLE


class CjSqlToJg:

    def __init__(self):
        self.cjsqlconn = pymysql.connect(host=CJSQLCON['HOST'], port=CJSQLCON['PORT'], db=CJSQLCON['DB'],
                                         user=CJSQLCON['USER'], passwd=CJSQLCON['PASSWD'], charset='utf8')
        self.jgsqlconn = pymysql.connect(host=JGSQLCON['HOST'], port=JGSQLCON['PORT'], db=JGSQLCON['DB'],
                                         user=JGSQLCON['USER'], passwd=JGSQLCON['PASSWD'], charset='utf8')
        self.cjurlcursor = self.cjsqlconn.cursor()
        self.jgurlcursor = self.jgsqlconn.cursor()

    def close(self):
        self.cjurlcursor.close()
        self.jgurlcursor.close()
        self.cjsqlconn.close()
        self.jgsqlconn.close()

    def exportsql(self):
        print(str(datetime.datetime.now()) + "：开始进行sql记录的自动导出")
        for cjtable in CJSQLTABLE:
            print(str(datetime.datetime.now()) + "：正在导出表%s" % cjtable)
            i = 0
            jgsysid = []
            jgtable = JGSQLTABLE[cjtable]
            if cjtable == "lawtext":
                cjsql = '''SELECT ID,LegalName,Organizaation,PublishDate,EffectiveDate,SortA,SortB,SortC,SortD,SORTE,
                SORTF,SORTG,Keyword,ChapNo,Articles,Chapter,Section,SYS_FLD_DIGITFILENAME,FileUrl,DownLoadUrl,
                DownLoadWebNameC,DownLoadWebNameE,SYSID from %s where SYS_FLD_MARKSTATE = "0" ORDER BY SortA''' % cjtable
            elif cjtable == "lawcasetext":
                cjsql = '''SELECT ID,Title,CaseNumber,KeyWord,People,SortA,CaseOfAction,UseLaw,AdjudicationDate,
                JudgAgency,SortB,SortC,CaseSummary,Articles,Chapter,Section,SYS_FLD_DIGITFILENAME,FileUrl,DownLoadUrl,
                DownLoadWebNameC,DownLoadWebNameE,SYSID from %s WHERE SYS_FLD_MARKSTATE = "0" and (SortA != "LAWCOUNTRYYDNXY" OR 
                (SortA = "LAWCOUNTRYYDNXY" AND Isconversion = "0")) ORDER BY SortA''' % cjtable
            elif cjtable == "treaty":
                cjsql = '''SELECT ID,Title,StateParty,PublishDate,EffectiveDate,SortA,SortB,SortC,Articles,Chapter,
                Section,SYS_FLD_DIGITFILENAME,FileUrl,DownLoadUrl,DownLoadWebNameC,DownLoadWebNameE,SYSID 
                FROM %s where SYS_FLD_MARKSTATE = "0" ORDER BY SortA''' % cjtable
            try:
                self.cjurlcursor.execute(cjsql)
                cjrs = self.cjurlcursor.fetchall()
            except Exception as e:
                print("%s：采集服务器查询出错:" % str(datetime.datetime.now()) + str(e))
                continue
            jgsql = '''SELECT SYSID FROM %s''' % jgtable
            try:
                self.jgurlcursor.execute(jgsql)
                jgrs = self.jgurlcursor.fetchall()
            except Exception as e:
                print("%s：加工服务器查询出错:" % str(datetime.datetime.now()) + str(e))
                continue
            for jgrsr in jgrs:  # 将查询的sysid更新为列表方便查询
                file_sql = jgrsr[0]
                jgsysid.append(file_sql)
            for cjr in cjrs:
                SYSID = cjr[-1]
                if SYSID not in jgsysid:
                    jgr = list(cjr)
                    for j in range(len(jgr)):
                        if jgr[j] is None:
                            jgr[j] = ''
                    markstate = (0,)
                    if jgtable == "`Law`":
                        jgr[7] = ""
                        jgr[10] = ""
                        jgrtuple = tuple(jgr) + markstate
                        jgsql = '''insert into %s (ID,LegalName,Organizaation,PublishDateStr,EffectiveDateStr, 
                            SortA,SortB, SortC, SortD,SORTE,SORTF,SORTG,Keyword,ChapNo,Articles,Chapter,Section, 
                            SYS_FLD_DIGITFILENAME,FileUrl,DownLoadUrl,DownLoadWebNameC,DownLoadWebNameE,SYSID, 
                            SYS_FLD_MARKSTATE) values %s''' % (jgtable, jgrtuple)
                    elif jgtable == "`case`":
                        jgrtuple = tuple(jgr) + markstate
                        jgsql = '''insert into %s (ID,Title,CaseNumber,KeyWord,People,SortA,CaseOfAction, 
                            UseLaw,AdjudicationDateStr,JudgAgency,SortB,SortC,CaseSummary,Articles,Chapter,Section, 
                            SYS_FLD_DIGITFILENAME, FileUrl,DownLoadUrl,DownLoadWebNameC,DownLoadWebNameE, 
                            SYSID,SYS_FLD_MARKSTATE) values %s''' % (jgtable, jgrtuple)
                    elif jgtable == "`treaty`":
                        jgrtuple = tuple(jgr) + markstate
                        jgsql = '''insert into %s (ID,Title,StateParty,PublishDateStr,EffectiveDateStr,SortA,SortB,
                        SortC,Articles,Chapter,Section,SYS_FLD_DIGITFILENAME,FileUrl,DownLoadUrl,DownLoadWebNameC,
                        DownLoadWebNameE,SYSID,SYS_FLD_MARKSTATE) values %s''' % (jgtable, jgrtuple)
                    try:
                        self.jgurlcursor.execute(jgsql)
                        cjsql = '''update %s set SYS_FLD_MARKSTATE = "1" where ID = "%s"''' % (cjtable, cjr[0])
                        try:
                            self.cjurlcursor.execute(cjsql)
                            self.jgsqlconn.commit()
                            self.cjsqlconn.commit()
                        except:
                            print("%s：采集服务器更新数据状态出错" % str(datetime.datetime.now()))
                    except Exception as e:
                        print("插入" + jgtable + "ID=" + str(cjr[0]) + "数据失败:" + str(e) + '\n')
                else:
                    cjsql = '''update %s set SYS_FLD_MARKSTATE = "1" where ID = "%s"''' % (cjtable, cjr[0])
                    try:
                        self.cjurlcursor.execute(cjsql)
                        self.jgsqlconn.commit()
                        self.cjsqlconn.commit()
                    except:
                        print("%s：采集服务器更新数据状态出错" % str(datetime.datetime.now()))
                i = i + 1
                print("\r已导出sql记录: %d" % i, end="")
        self.close()
        print(str(datetime.datetime.now()) + "：sql记录的自动导出结束")
        return True
